IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetActiveOrInactiveProtocols]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetActiveOrInactiveProtocols]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************                    
* Name:   [GetActiveOrInactiveProtocols]  
* Purpose:  Retrieves corresponding Protocol deatils matching the Protocol Ids.        
*                    
* PARAMETERS(IN)                    
* Name    Description                         
* -------------     -------------------------------------------                    
* @delimittedString    ntext of delimitted string  
* @domainUserId		   FrameworkDomainUserId of the looged in(or Impersonated) user-Required  
*********************************************************************/                    
                    
CREATE PROCEDURE [dbo].[GetActiveOrInactiveProtocols]
(                    
@delimittedString ntext,    
@domainUserId  int                  
)                    
AS BEGIN                    
SET NOCOUNT ON                    
                    
Declare @protocolIds table (Sequence int identity, [ID] int,[ProtocolId] int)                      
insert into @protocolIds select [Id],[Value] from [GetValueTableOfDelimittedString] (@delimittedString,',')                      
                 
select protocol.[id]      
  ,protocol.title               
  ,protocol.expirationdate      
  ,case when len(protocol.SubStatusId) > 0 then      
   status.[name] + ', ' + substatus.[Name]       
   else status.[name] end as 'overallstatus'      
  ,protocol.pendingirb      
  ,piperson.lastname + ', ' +  piperson.firstname as 'piname'      
  ,protocol.protocolnumber      
  ,sponsor.[Name] as 'SponsorName'       
  ,protocol.PIFwkDomainOrganizationId as 'OrganizationId'        
  ,protocol.PiPersonId      
  ,protocol.Version      
  ,ara.AccessLevel       
  ,count(document.[Id]) as ConsentCount      
  ,cast((case when count(process.[Id]) = 0 then '0' else '1' end) as bit) as IsLegacy      
  ,protocol.IsLocked      
 from @protocolIds protocolTemp    
 Inner Join Protocol    
 On protocolTemp.[ProtocolId]= protocol.[Id]    
  inner join dbo.fnadmqgetauthorizedresourcelist(@domainUserId, 2) ara       
   on ara.resourceid = protocol.fwkresourceid       
  inner join ProtocolOverallStatusType status      
   on protocol.OverallStatusId = status.[id]      
  left join ProtocolSubStatusType substatus      
   on protocol.SubStatusId = substatus.[id]      
  left join admperson piperson      
   on piperson.[id] = protocol.PIPersonId      
  left join admsponsor sponsor      
   on protocol.admsponsorid = sponsor.[id]      
  left join ProtocolDocument document      
   on protocol.[Id] = document.ProtocolId and      
     document.iscurrent = 1 and document.type='ATCHTYPE_CNSNTFRM' and document.HardCopy = 0 and document.published = 1      
  left join ProtocolProcess process      
   on protocol.[Id] = process.ProtocolId and process.Type = 'PPTYPE_IR'      
 group by protocol.[id]      
  ,protocol.title               
  ,protocol.expirationdate      
  ,protocol.SubStatusId      
  ,status.[name]      
  ,substatus.[Name]       
  ,protocol.pendingirb      
  ,piperson.lastname      
  ,piperson.firstname      
  ,protocol.protocolnumber      
  ,sponsor.[Name]      
  ,protocol.PIFwkDomainOrganizationId      
  ,protocol.PiPersonId      
  ,protocol.Version      
  ,ara.AccessLevel       
  ,protocol.IsLocked  
  ,protocolTemp.[Id]   
 order by protocolTemp.[Id]      
                    
END 